﻿'''
功能：把web表单的数据存入数据库，根据原有记录生产csv文件名并返回
'''

# 导入需要的库
import pyodbc
from copy import deepcopy
from datetime import datetime


# # 将表单相关信息存为列表，方便数据处理,依次是工单号，工号，重工，抽测，特采，库存，备注，上传文件夹，原文件名
# form_infos = [form.mo_id.data,machineID, form.user_id.data, int(form.rework.data), int(form.sampling.data), \
#               int(form.concession.data), int(form.inventory.data), form.notes.data, uploadsDir, f.filename]


def infos_to_sql(form_infos, conn, logger):
    cursor = conn.cursor()
    toDatabase = deepcopy(form_infos)

    # 查询对于工单的LOTID是否存在，存在+1，不存在初始化为1
    cursor.execute(
        "SELECT MAX(LotID) FROM Sorting_Info WHERE MoID='{}';".format(form_infos[0]))
    databaseLotID = cursor.fetchall()
    # print(databaseLotID)
    if databaseLotID[0][0] == None:
        lotID = 1
    else:
        lotID = databaseLotID[0][0] + 1

    # 添加LOTID到列表
    toDatabase.insert(1, lotID)
    # 添加Status
    toDatabase.insert(2, -1)

    # 插入当前时间
    toDatabase.insert(12, str(datetime.now()))
    logger.info(f'工单:{toDatabase[0]},工号:{toDatabase[4]}\n机台:{toDatabase[3]},原文件名:{toDatabase[11]}')
    try:
        cursor.execute(
            "INSERT INTO Sorting_Info(MoID,LotID,LotStatus,MachineID,UserID,Rework,Sampling,Concession,"
            "Inventory,Notes,UploadPath,OriginalFileName,UploadTime) VALUES{};".format(
                tuple(toDatabase)))
    except Exception as e:
        conn.rollback()
        logger.error(e)
    else:
        conn.commit()
        logger.info('上传文件信息成功！')

    # conn.close()
    return lotID


def delete_info(moid, lotid, conn, logger):
    """
    失败后删除已存储的info信息
    :param lotid:
    :param conn:
    :param logger:
    :return:
    """
    cursor = conn.cursor()
    cursor.execute(f"DELETE FROM Sorting_Info WHERE MoID='{moid}' AND LotID='{lotid}'")
    logger.info(f'删除第{lotid}次info信息')
    conn.commit()


if __name__ == '__main__':
    form_infos = ['5101-190876', 0, '19999', 1, 0, 0, 1,
                  '测试数据', 'uploads\\2020\\3', '5101-20020086-0-319-M.csv']

    lotID = infos_to_sql(form_infos)
    print(lotID)
